package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.common.Format;
import com.powerunion.datacollection.common.UUIDGenerator;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.SubFormDataDao;
import com.powerunion.datacollection.model.CascadeFieldSet;
import com.powerunion.datacollection.model.Column;
import com.powerunion.datacollection.model.MaintblData;
import com.powerunion.datacollection.model.SubFormSet;
import com.powerunion.datacollection.model.SubmissionWorkItem;
import com.powerunion.datacollection.model.SysServer;
import com.powerunion.datacollection.model.User;
import com.powerunion.datacollection.model.jsonObject.DBTableField;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.datacollection.service.CascadeFieldManager;
import com.powerunion.datacollection.service.ColumnManager;
import com.powerunion.datacollection.service.MaintblDataManager;
import com.powerunion.datacollection.service.SubFormSetManager;
import com.powerunion.datacollection.service.UserManager;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class SubFormDataDaoImpl extends JdbcBase implements SubFormDataDao {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			SubFormDataDaoImpl.class);
	private com.powerunion.datacollection.util.DBUtil dbUtils = null;

	public SubFormDataDaoImpl() {
		super();
		dbUtils = new com.powerunion.datacollection.util.DBUtil();
	}

	public boolean save4MainTbl(MaintblData data, String formValues)
			throws DaoException {
		SubFormSetManager sfm = new SubFormSetManager();
		MaintblDataManager mdm = new MaintblDataManager();
		Connection conn = null;

		try {
			SubFormSet sfs = sfm.findFormSetByKey4MaintTbl(data.getFormId());
			SysServer server = sfm.getSysServer(sfs.getSvrName());
			List fields = mdm.getMaintblFields(data.getFormId());
			// 把表单值转换成json对象
			JSONObject fieldObject = JSONObject.fromObject(formValues);
			StringBuffer sql = new StringBuffer();
			sql.append(" insert into ").append(sfs.getTblName()).append("(");

			StringBuffer fieldStr = new StringBuffer();
			fieldStr.append(sfs.getKeyFld());
			for (Iterator iter = fields.iterator(); iter.hasNext();) {
				DBTableField f = (DBTableField) iter.next();
				String fieldName = f.getFieldName();
				fieldStr.append(",").append(fieldName);
			}
			sql.append(fieldStr.toString()).append(") values(?");
			StringBuffer valueStr = new StringBuffer();
			for (int i = 0; i < fields.size(); i++) {
				valueStr.append(",").append("?");
			}
			sql.append(valueStr.toString()).append(")");
			logger.debug("MaintblData sql:" + sql.toString());
			conn = dbUtils.getTblConnection(server);

			SubFormSet sufFormSet = sfm.findFormSetByKey4SubTbl(data
					.getFormId());
			CascadeFieldManager cfm = new CascadeFieldManager();
			List foreignKeies = cfm.getCascadeFields(data.getFormId());
			JSONObject mainTblValue = this.getMaintblDataInfo4SubFormMaintTbl(
					data, Constants.FORM_DATA_TYPE_SAVE);
			if (!mainTblValue.isEmpty()) {
				// 组装删除的sql语句
				String deleteSql = this.createDeleteSql(sufFormSet,
						foreignKeies);
				// 删除原先从表里的数据
				this.deleteSubformData(conn, deleteSql, foreignKeies,
						mainTblValue);
			}

			StringBuffer delSql = new StringBuffer();
			delSql.append(" delete from ").append(sfs.getTblName()).append(
					" where ").append(sfs.getKeyFld()).append(" = ?");

			mdm.deleteFormData(data, conn, delSql.toString());

			UpdateOperator update = new UpdateOperator();
			update.setSql(sql.toString());
			update.setUseOnlyConnectionFlag(true);
			update.setPresentConnection(conn);

			String seq = UUIDGenerator.generate4Number();
			data.setFormKey(seq);
			if (sfs.getKeyType().toLowerCase().equals("number")) {
				update.addParameter(new SqlParameter(SqlType.BIGINT, new Long(
						Long.parseLong(seq))));
			} else {
				update.addParameter(new SqlParameter(SqlType.VARCHAR, seq));
			}
			for (Iterator iter = fields.iterator(); iter.hasNext();) {
				DBTableField f = (DBTableField) iter.next();
				String fieldName = f.getFieldName();
				String fieldValues = fieldObject.getString(fieldName);
				int fieldType = dbUtils.getSqlTypes(f.getFieldType());
				if (fieldType == SqlType.DATE) {
					Date date = Format.getInstance().stringToDate(fieldValues,
							"yyyy-MM-dd");
					if (date != null) {
						update.addParameter(new SqlParameter(SqlType.DATE,
								new java.sql.Date(date.getTime())));
					} else {
						update.addParameter(new SqlParameter(fieldType, null));
					}
				} else {
					update
							.addParameter(new SqlParameter(fieldType,
									fieldValues));
				}
			}
			if (fields.size() > 0) {
				update.access();
			}
			int effectCount = update.getEffectCounts();
			if (effectCount > 0) {
				return mdm.saveMaintblData(data);
			} else {
				return false;
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("保存主表报送信息出错！");
		} finally {
			DBUtils.close(conn);
		}
	}

	public boolean save4SubTbl(MaintblData data, List formValues)
			throws DaoException {
		SubFormSetManager sfm = new SubFormSetManager();
		Connection conn = null;
		try {
			SubFormSet sfs = sfm.findFormSetByKey4SubTbl(data.getFormId());
			SysServer server = sfm.getSysServer(sfs.getSvrName());
			ColumnManager cm = new ColumnManager();
			List fields = cm.getColumnsByFormId(data.getFormId());
			CascadeFieldManager cfm = new CascadeFieldManager();
			List foreignKeies = cfm.getCascadeFields(data.getFormId());
			String sql = this.createInsertSql(sfs, fields, foreignKeies);
			logger.debug("MaintblData sql:" + sql.toString());

			conn = dbUtils.getTblConnection(server);

			JSONObject mainTblValue = this.getMaintblDataInfo4SubFormMaintTbl(
					data, Constants.FORM_DATA_TYPE_SAVE);
			UpdateOperator update = new UpdateOperator();
			update.setSql(sql.toString());
			update.setUseOnlyConnectionFlag(true);
			update.setPresentConnection(conn);

			for (Iterator iter = formValues.iterator(); iter.hasNext();) {
				SqlParameterSet spSet = new SqlParameterSet();
				JSONObject formValue = (JSONObject) iter.next();

				String seq = UUIDGenerator.generate4Number();
				// data.setFormKey(seq);
				if (sfs.getKeyType().toLowerCase().equals("number")) {
					spSet.addParameter(new SqlParameter(SqlType.BIGINT,
							new Long(Long.parseLong(seq))));
				} else {
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR, seq));
				}
				for (Iterator fiter = fields.iterator(); fiter.hasNext();) {
					Column c = (Column) fiter.next();
					String fieldName = c.getDataIndex();
					String fieldValues = formValue.getString(fieldName);
					String fieldTypeStr = c.getFieldType();
					int fieldType = dbUtils.getSqlTypes(fieldTypeStr);

					if (fieldType == SqlType.DATE) {
						Date date = Format.getInstance().stringToDate(
								fieldValues, "yyyy-MM-dd");
						if (date != null) {
							spSet.addParameter(new SqlParameter(SqlType.DATE,
									new java.sql.Date(date.getTime())));
						} else {
							spSet
									.addParameter(new SqlParameter(fieldType,
											null));
						}
					} else {
						if (fieldTypeStr.toLowerCase().equals("varchar2")
								|| fieldTypeStr.toLowerCase().equals("char")) {
							spSet.addParameter(new SqlParameter(fieldType,
									fieldValues));
						} else if (fieldTypeStr.toLowerCase().equals("number")) {
							spSet.addParameter(new SqlParameter(fieldType,
									new Long(Long.parseLong(fieldValues))));
						}
					}
				}

				for (Iterator kiter = foreignKeies.iterator(); kiter.hasNext();) {
					CascadeFieldSet f = (CascadeFieldSet) kiter.next();
					String fieldName = f.getMaintblField();
					String fieldValues = mainTblValue.getString(fieldName);
					String fieldTypeStr = f.getMaintFieldType();
					int fieldType = dbUtils.getSqlTypes(f.getMaintFieldType());
					if (fieldType == SqlType.DATE) {
						Date date = Format.getInstance().stringToDate(
								fieldValues, "yyyy-MM-dd");
						if (date != null) {
							spSet.addParameter(new SqlParameter(SqlType.DATE,
									new java.sql.Date(date.getTime())));
						} else {
							spSet
									.addParameter(new SqlParameter(fieldType,
											null));
						}
					} else {
						if (fieldTypeStr.toLowerCase().equals("varchar2")
								|| fieldTypeStr.toLowerCase().equals("char")) {
							spSet.addParameter(new SqlParameter(fieldType,
									fieldValues));
						} else if (fieldTypeStr.toLowerCase().equals("number")) {
							spSet.addParameter(new SqlParameter(fieldType,
									new Long(Long.parseLong(fieldValues))));
						}
					}
				}
				update.addBatchParameter(spSet);
			}
			if (fields.size() > 0) {
				update.access();
			}

			int[] effectCount = update.getBatchEffectCounts();
			if (effectCount.length > 0) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("保存从表报送信息出错！");
		} finally {
			DBUtils.close(conn);
		}
	}

	public JSONObject getMaintblDataInfo4SubFormMaintTbl(MaintblData data,
			int requestType) throws DaoException {
		Connection conn = null;
		Connection localConnection = null;
		SubFormSetManager sfm = new SubFormSetManager();
		MaintblDataManager mdm = new MaintblDataManager();
		JSONObject result = new JSONObject();
		try {
			localConnection = this.getConnection();
			SubFormSet sf = sfm.findFormSetByKey4MaintTbl(data.getFormId());
			SysServer server = sfm.getSysServer(sf.getSvrName());
			List fields = mdm.getMaintblFields(data.getFormId());

			conn = dbUtils.getTblConnection(server);
			StringBuffer sql = new StringBuffer();
			sql.append(" select ");
			sql.append(sf.getKeyFld());
			for (int i = 0; i < fields.size(); i++) {
				DBTableField f = (DBTableField) fields.get(i);
				String fieldName = f.getFieldName();
				sql.append(",");
				sql.append(fieldName);
			}
			sql.append(" from ").append(sf.getTblName()).append(" where ")
					.append(sf.getKeyFld()).append(" =?");
			logger.debug("getMaintblDataInfo4SubFormMaintTbl sql:"
					+ sql.toString());
			String formKey = "";
			if (requestType == Constants.FORM_DATA_TYPE_INIT) {
				formKey = mdm.getMaintblDataByKey4Init(data, localConnection);
			} else {
				formKey = mdm.getMaintblDataByKey(data, localConnection);
			}

			QueryOperator query = new QueryOperator();
			query.setSql(sql.toString());
			query.setUseOnlyConnectionFlag(true);
			query.setPresentConnection(conn);

			query.addParameter(new SqlParameter(SqlType.VARCHAR, formKey));
			query.access();
			if (query.resultSetAvailable()) {

				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());

				if (rs.rowCount > 0) {
					result
							.put(sf.getKeyFld(), rs.getFieldValue(sf
									.getKeyFld()));
					for (Iterator iter = fields.iterator(); iter.hasNext();) {
						DBTableField f = (DBTableField) iter.next();
						String fieldName = f.getFieldName();
						if (f.getFieldType().equals("DATE")) {
							Date fieldValue = rs.getFieldValueAsDate(fieldName);
							String dateStr = Format.getInstance().dateToYMD(
									fieldValue);
							result.put(fieldName, dateStr);
						} else {
							Object fieldValue = rs.getFieldValue(fieldName);
							if (fieldValue == null) {
								result.put(fieldName, "");
							} else {
								result.put(fieldName, fieldValue);
							}

						}

					}
				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("查找主表报送区数据出错！");
		} finally {
			DBUtils.close(conn);
			DBUtils.close(localConnection);
		}
		return result;
	}

	private boolean deleteSubformData(Connection conn, String sql,
			List foreignKeies, JSONObject mainTblValue) throws DaoException {
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		logger.debug("deleteSubformData sql:" + sql);
		for (int i = 0; i < foreignKeies.size(); i++) {
			CascadeFieldSet cfs = (CascadeFieldSet) foreignKeies.get(i);
			// 主表字段
			String mainTblField = cfs.getMaintblField();
			// 从表字段
			String subTblField = cfs.getSubTblField();
			String maintFieldType = cfs.getMaintFieldType();
			String subFieldType = cfs.getSubFieldType();
			int fieldType = dbUtils.getSqlTypes(maintFieldType);
			String value = mainTblValue.getString(mainTblField);
			if (maintFieldType.toLowerCase().equals("varchar2")
					|| maintFieldType.toLowerCase().equals("char")) {
				update.addParameter(new SqlParameter(fieldType, value));
			} else if (maintFieldType.toLowerCase().equals("number")) {
				update.addParameter(new SqlParameter(fieldType, new Long(Long
						.parseLong(value))));
			}
		}
		try {
			update.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("delete throw of error!");
		}

		int effectCount = update.getEffectCounts();
		if (effectCount > 0) {
			return true;
		} else {
			return false;
		}
	}

	private String createInsertSql(SubFormSet sfs, List fields,
			List foreignKeies) {
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into ").append(sfs.getTblName()).append("(");

		StringBuffer fieldStr = new StringBuffer();
		fieldStr.append(sfs.getKeyFld());
		int fieldCount = 0;
		for (Iterator iter = fields.iterator(); iter.hasNext();) {
			Column c = (Column) iter.next();
			String fieldName = c.getDataIndex();
			if (!fieldName.equals(sfs.getKeyFld())) {
				fieldStr.append(",").append(fieldName);
				fieldCount++;
			}

		}
		for (Iterator iter = foreignKeies.iterator(); iter.hasNext();) {
			CascadeFieldSet f = (CascadeFieldSet) iter.next();
			String fieldName = f.getSubTblField();
			if (!fieldName.equals(sfs.getKeyFld())) {
				fieldStr.append(",").append(fieldName);
				fieldCount++;
			}
		}

		sql.append(fieldStr.toString()).append(") values(?");
		StringBuffer valueStr = new StringBuffer();
		for (int i = 0; i < fieldCount; i++) {
			valueStr.append(",").append("?");
		}
		sql.append(valueStr.toString()).append(")");
		return sql.toString();
	}

	private String createSelectSql(SubFormSet sfs, List foreignKeies,
			JSONObject mainTblValue) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ").append(sfs.getTblName());

		StringBuffer where = new StringBuffer();

		for (Iterator iter = foreignKeies.iterator(); iter.hasNext();) {
			CascadeFieldSet f = (CascadeFieldSet) iter.next();
			String mainFieldName = f.getMaintblField();
			String subFieldName = f.getSubTblField();
			String fieldValues = mainTblValue.getString(mainFieldName);
			String fieldTypeStr = f.getMaintFieldType();

			int fieldType = dbUtils.getSqlTypes(fieldTypeStr);
			if (StringUtils.isEmpty(where.toString())) {
				where.append(subFieldName).append("='");
			} else {
				where.append(" and ").append(subFieldName).append("='");
			}
			if (fieldType == SqlType.DATE) {
				Date date = Format.getInstance().stringToDate(fieldValues,
						"yyyy-MM-dd");
				if (date != null) {
					where.append(new java.sql.Date(date.getTime())).append("'");
				}
			} else {
				if (fieldTypeStr.toLowerCase().equals("varchar2")
						|| fieldTypeStr.toLowerCase().equals("char")) {
					where.append(fieldValues).append("'");
				} else if (fieldTypeStr.toLowerCase().equals("number")) {
					where.append(new Long(Long.parseLong(fieldValues))).append(
							"'");
				}
			}

			if (!StringUtils.isEmpty(where.toString())) {
				sql.append(" where ").append(where.toString());
			}
			logger.info("createSelectSql:" + sql.toString());
			return sql.toString();
		}

		return sql.toString();
	}

	private String createDeleteSql(SubFormSet sfs, List foreignKies) {
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from ").append(sfs.getTblName()).append(" where ");
		StringBuffer where = new StringBuffer();
		for (Iterator iter = foreignKies.iterator(); iter.hasNext();) {
			CascadeFieldSet f = (CascadeFieldSet) iter.next();
			if (StringUtils.isEmpty(where.toString())) {
				where.append(f.getSubTblField()).append("=?");
			} else {
				where.append(",").append(f.getSubTblField()).append("=?");
			}
		}
		sql.append(where.toString());
		logger.info("createDeleteSql:" + sql.toString());
		return sql.toString();
	}

	public List getSubFormData(MaintblData data, int requestType)
			throws DaoException {
		List result = new ArrayList();
		result = this.getData(data, requestType, false);
		return result;

	}

	private List getData(MaintblData data, int requestType, boolean isCollect) throws DaoException {
		List result = new ArrayList();
		Connection conn = null;
		QueryOperator query = new QueryOperator();
		try {
			UserManager um = new UserManager();
			SubFormSetManager sfm = new SubFormSetManager();
			SubFormSet sfs = sfm.findFormSetByKey4SubTbl(data.getFormId());
			JSONObject mainTblValue = this.getMaintblDataInfo4SubFormMaintTbl(
					data, requestType);
			if (mainTblValue.isEmpty()) {
				return result;
			}
			CascadeFieldManager cfm = new CascadeFieldManager();
			List foreignKeies = cfm.getCascadeFields(data.getFormId());
			ColumnManager cm = new ColumnManager();
			List fields = cm.getColumnsByFormId(data.getFormId());
			String sql = this.createSelectSql(sfs, foreignKeies, mainTblValue);

			SysServer server = sfm.getSysServer(sfs.getSvrName());
			conn = dbUtils.getTblConnection(server);

			query.setSql(sql);
			query.setUseOnlyConnectionFlag(true);
			query.setPresentConnection(conn);
			query.access();
			
			if (query.resultSetAvailable()) {
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if (rs.rowCount > 0) {
					for (int i = 0; i < rs.rowCount; i++) {
						JSONObject info = new JSONObject();
						for (Iterator iter = fields.iterator(); iter.hasNext();) {
							Column c = (Column) iter.next();
							String fieldName = c.getDataIndex();
							if (c.getFieldType().equals("DATE")) {
								Date fieldValue = rs
										.getFieldValueAsDate(fieldName);
								String dateStr = Format.getInstance()
										.dateToYMD(fieldValue);
								info.put(fieldName, dateStr);
							} else {
								Object fieldValue = rs.getFieldValue(fieldName);
								info.put(fieldName, fieldValue);
							}
						}

						if (isCollect) {// 数据汇总添加 报送人姓名
							User user = um.getUser(data.getSubmiUser());
							info.put("statff", user.getUserName());
						}

						result.add(info);
						rs.next();
					}
				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getSubFormData4Template throw error!");
		} finally {
			DBUtils.close(conn);
		}
		return result;

	}

	public List getSubFormData4Approve(String procId, List submissionWorkItems,
			int requestType) throws DaoException {
		List result = new ArrayList();
		try {
			if (submissionWorkItems == null || submissionWorkItems.isEmpty()) {
				return result;
			}

			for (Iterator iters = submissionWorkItems.iterator(); iters
					.hasNext();) {
				SubmissionWorkItem sw = (SubmissionWorkItem) iters.next();
				MaintblData data = new MaintblData();
				data.setActId(sw.getId());
				data.setFormId(sw.getFormId());
				data.setProcId(procId);
				data.setSubmiUser(sw.getStaffId());

				List subValues = this.getData(data, requestType, true);

				result.addAll(subValues);
			}

		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getSubFormData4Approve throw error!");
		}
		return result;

	}

}
